{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       From       To       VaR     CoVaR    ΔCoVaR     %CoVaR         α  \\\n",
      "0    DLSETI    DLSSE -2.034665 -2.689305 -0.654639  32.174308 -1.974748   \n",
      "1     DLSSE   DLSETI -1.930379 -2.386624 -0.456245  23.635015 -1.899399   \n",
      "2    DLTWII    DLSSE -2.034665 -2.592601 -0.557935  27.421481 -1.920317   \n",
      "3     DLSSE   DLTWII -1.926632 -2.449166 -0.522534  27.121650 -1.875302   \n",
      "4    DLJKSE    DLSSE -2.034665 -2.549579 -0.514914  25.307047 -1.976206   \n",
      "5     DLSSE   DLJKSE -1.722887 -2.253592 -0.530705  30.803243 -1.721901   \n",
      "6    DLKLCI    DLSSE -2.034665 -2.529157 -0.494492  24.303340 -1.971063   \n",
      "7     DLSSE   DLKLCI -1.088068 -1.372735 -0.284666  26.162537 -1.091792   \n",
      "8   DLBSESN    DLSSE -2.034665 -2.413596 -0.378931  18.623753 -1.965059   \n",
      "9     DLSSE  DLBSESN -1.937299 -2.415293 -0.477994  24.673192 -1.919171   \n",
      "10  DLKOSPI    DLSSE -2.034665 -2.440730 -0.406065  19.957322 -1.954684   \n",
      "11    DLSSE  DLKOSPI -2.269648 -2.818821 -0.549173  24.196384 -2.173559   \n",
      "12    DLPSI    DLSSE -2.034665 -2.431520 -0.396854  19.504648 -1.986828   \n",
      "13    DLSSE    DLPSI -1.714726 -2.043001 -0.328275  19.144456 -1.710689   \n",
      "\n",
      "           β        t_α       t_β  \\\n",
      "0   0.370164 -33.993136  5.893973   \n",
      "1   0.239462 -45.316061  5.169387   \n",
      "2   0.348943 -33.295267  5.210871   \n",
      "3   0.282044 -37.833949  5.102342   \n",
      "4   0.332798 -30.142779  3.890418   \n",
      "5   0.261316 -32.022209  4.415611   \n",
      "6   0.512922 -32.595214  4.338528   \n",
      "7   0.138078 -40.762938  4.735179   \n",
      "8   0.231527 -26.445027  2.773338   \n",
      "9   0.243835 -42.696054  5.124270   \n",
      "10  0.214150 -36.618277  4.113182   \n",
      "11  0.317134 -36.951908  5.429590   \n",
      "12  0.259337 -34.554348  3.249844   \n",
      "13  0.163325 -36.486649  3.054994   \n",
      "\n",
      "                                              Formula  \n",
      "0   R_0.05^SSE = (-1.9747) + (0.3702) * R_0.05^DLSETI  \n",
      "1   R_0.05^DLSETI = (-1.8994) + (0.2395) * R_0.05^SSE  \n",
      "2   R_0.05^SSE = (-1.9203) + (0.3489) * R_0.05^DLTWII  \n",
      "3   R_0.05^DLTWII = (-1.8753) + (0.2820) * R_0.05^SSE  \n",
      "4   R_0.05^SSE = (-1.9762) + (0.3328) * R_0.05^DLJKSE  \n",
      "5   R_0.05^DLJKSE = (-1.7219) + (0.2613) * R_0.05^SSE  \n",
      "6   R_0.05^SSE = (-1.9711) + (0.5129) * R_0.05^DLKLCI  \n",
      "7   R_0.05^DLKLCI = (-1.0918) + (0.1381) * R_0.05^SSE  \n",
      "8   R_0.05^SSE = (-1.9651) + (0.2315) * R_0.05^DLB...  \n",
      "9   R_0.05^DLBSESN = (-1.9192) + (0.2438) * R_0.05...  \n",
      "10  R_0.05^SSE = (-1.9547) + (0.2142) * R_0.05^DLK...  \n",
      "11  R_0.05^DLKOSPI = (-2.1736) + (0.3171) * R_0.05...  \n",
      "12   R_0.05^SSE = (-1.9868) + (0.2593) * R_0.05^DLPSI  \n",
      "13   R_0.05^DLPSI = (-1.7107) + (0.1633) * R_0.05^SSE  \n",
      "Results saved to risk_spillover_with_t_statistics.csv\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import statsmodels.api as sm\n",
    "import statsmodels.formula.api as smf\n",
    "\n",
    "# Load the dataset\n",
    "file_path = 'F:/11月paper/COVAR/c2/data.xlsx'\n",
    "sheet2_data = pd.read_excel(file_path, sheet_name='Sheet2')\n",
    "\n",
    "# Rename columns for easier handling\n",
    "sheet2_data.rename(columns={'Unnamed: 0': 'Date'}, inplace=True)\n",
    "sheet2_data['Date'] = pd.to_datetime(sheet2_data['Date'])\n",
    "\n",
    "# Define regions and China's stock market column\n",
    "regions = ['DLSETI', 'DLTWII', 'DLJKSE', 'DLKLCI', 'DLBSESN', 'DLKOSPI', 'DLPSI']\n",
    "china_stock = 'DLSSE'  # China's stock market column\n",
    "\n",
    "# Define quantile level (tau) for quantile regression\n",
    "tau = 0.05  # Corresponding to 95% confidence level\n",
    "\n",
    "# Perform quantile regression\n",
    "def quantile_regression(data, dependent, independent, tau):\n",
    "    formula = f\"{dependent} ~ {independent}\"\n",
    "    model = smf.quantreg(formula, data)\n",
    "    result = model.fit(q=tau)\n",
    "    return result\n",
    "\n",
    "# Function to calculate VaR, CoVaR, ΔCoVaR, %CoVaR, coefficients, and t-statistics\n",
    "def calculate_covar_and_coefficients(data, dependent, independent, tau):\n",
    "    model_result = quantile_regression(data, dependent, independent, tau)\n",
    "    alpha = model_result.params['Intercept']\n",
    "    beta = model_result.params[independent]\n",
    "    t_stats = model_result.tvalues  # Extract t-statistics\n",
    "    t_alpha = t_stats['Intercept']\n",
    "    t_beta = t_stats[independent]\n",
    "    var_independent = np.percentile(data[independent], tau * 100)\n",
    "    var_dependent = np.percentile(data[dependent], tau * 100)\n",
    "    covar = alpha + beta * var_independent\n",
    "    delta_covar = covar - var_dependent\n",
    "    percent_covar = (delta_covar / var_dependent) * 100\n",
    "    return {\n",
    "        'VaR': var_dependent,\n",
    "        'CoVaR': covar,\n",
    "        'ΔCoVaR': delta_covar,\n",
    "        '%CoVaR': percent_covar,\n",
    "        'α': alpha,\n",
    "        'β': beta,\n",
    "        't_α': t_alpha,\n",
    "        't_β': t_beta\n",
    "    }\n",
    "\n",
    "# Store results and quantile regression coefficients\n",
    "results = []\n",
    "\n",
    "for region in regions:\n",
    "    # Region -> China (DLSSE)\n",
    "    to_china = calculate_covar_and_coefficients(sheet2_data, china_stock, region, tau)\n",
    "    results.append({\n",
    "        'From': region,\n",
    "        'To': china_stock,\n",
    "        'VaR': to_china['VaR'],\n",
    "        'CoVaR': to_china['CoVaR'],\n",
    "        'ΔCoVaR': to_china['ΔCoVaR'],\n",
    "        '%CoVaR': to_china['%CoVaR'],\n",
    "        'α': to_china['α'],\n",
    "        'β': to_china['β'],\n",
    "        't_α': to_china['t_α'],\n",
    "        't_β': to_china['t_β'],\n",
    "        'Formula': f\"R_0.05^SSE = ({to_china['α']:.4f}) + ({to_china['β']:.4f}) * R_0.05^{region}\"\n",
    "    })\n",
    "\n",
    "    # China (DLSSE) -> Region\n",
    "    to_region = calculate_covar_and_coefficients(sheet2_data, region, china_stock, tau)\n",
    "    results.append({\n",
    "        'From': china_stock,\n",
    "        'To': region,\n",
    "        'VaR': to_region['VaR'],\n",
    "        'CoVaR': to_region['CoVaR'],\n",
    "        'ΔCoVaR': to_region['ΔCoVaR'],\n",
    "        '%CoVaR': to_region['%CoVaR'],\n",
    "        'α': to_region['α'],\n",
    "        'β': to_region['β'],\n",
    "        't_α': to_region['t_α'],\n",
    "        't_β': to_region['t_β'],\n",
    "        'Formula': f\"R_0.05^{region} = ({to_region['α']:.4f}) + ({to_region['β']:.4f}) * R_0.05^SSE\"\n",
    "    })\n",
    "\n",
    "# Convert results to DataFrame for visualization\n",
    "results_df = pd.DataFrame(results)\n",
    "\n",
    "# Display the results in the console\n",
    "print(results_df)\n",
    "\n",
    "# Optionally, save the results to a CSV file\n",
    "output_file = \"risk_spillover_with_t_statistics.csv\"\n",
    "results_df.to_csv(output_file, index=False)\n",
    "print(f\"Results saved to {output_file}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python (myenv)",
   "language": "python",
   "name": "myenv"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
